# -*- encoding:utf-8 -*-
u"""同步信息系统."""


import json
import warnings
import urllib3
from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor

from config import config
from lib import auth
from lib.table import table
from lib.validform import V, ValidateForm
from lib.funcs import validateAnguan

urllib3.disable_warnings()
warnings.filterwarnings("ignore")

app = Blueprint(__name__ + "_app", __name__)


@app.before_request
def setupdb():
    u"""构造函数."""
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    u"""析构函数."""
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


@app.route('/api/sync/systemNode/new_list_add', methods=['POST'])
@auth.permission('systemNode')
def get_newList_add(_currUser):
    u"""查询同步系统节点变更详情新增."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_system_node' and e.operating = 'add' and e.person_id = '%s' and e.state = '1'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
            from(SELECT a.sys_node_id as asys_node_id,
             a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, 
             a.systemnode_tag_change as systemnode_tag_change,
             a.systemnode_type_change as systemnode_type_change, a.systemnode_oper_change as systemnode_oper_change, a.level_change as level_change, 
            a.node_change_info as node_change_info, 
            d2t(e.r_time) as er_time, 
            a.system_change as asystem_change
            from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s) a """ % (tb.where(['a.systemnode_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT 
        a.sys_node_id as asys_node_id, 
        a.systemnode_name_change as systemnode_name_change,
         a.systemnode_code_change as systemnode_code_change,
          a.systemnode_type_change as systemnode_type_change,
        a.systemnode_tag_change as systemnode_tag_change,
         a.systemnode_oper_change as systemnode_oper_change,
        a.level_change ||'级' as level_change,
        a.node_change_info as node_change_info,
        d2t(e.r_time) as er_time,
        a.system_change as asystem_change,
        a.node_change_info as anode_change_info
        from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s %s %s %s """ % (tb.where(['a.systemnode_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '异常', "warn": str(e)})


@app.route('/api/sync/systemNode/new_list_update', methods=['POST'])
@auth.permission('systemNode')
def get_newList_edit(_currUser):
    u"""查询同步网站变更详情更改."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_system_node' and e.operating = 'update' and e.person_id = '%s' and e.state = '1'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
            from(SELECT a.sys_node_id as asys_node_id, 
            a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, 
            a.systemnode_tag_change as systemnode_tag_change,a.systemnode_type_change as systemnode_type_change, a.systemnode_oper_change as systemnode_oper_change,
            a.level_change as level_change, 
            a.node_change_info as node_change_info, 
            d2t(e.r_time) as er_time, 
            a.system_change as asystem_change
            from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s  group by e.r_time, a.sys_node_id,a.systemnode_name_change ,a.systemnode_code_change,a.systemnode_type_change,a.systemnode_tag_change,a.systemnode_oper_change, a.level_change,a.system_change,a.node_change_info) a """ % (tb.where(['a.systemnode_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT a.sys_node_id as asys_node_id, a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, a.systemnode_type_change as systemnode_type_change, 
        a.systemnode_tag_change as systemnode_tag_change,
        a.systemnode_oper_change as systemnode_oper_change,
        a.level_change ||'级' as level_change,
        a.node_change_info as node_change_info,
        d2t(e.r_time) as er_time,
        a.system_change as asystem_change,
        a.node_change_info as anode_change_info
        from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s
         group by e.r_time, a.sys_node_id,a.systemnode_name_change ,a.systemnode_code_change,a.systemnode_type_change,a.systemnode_tag_change,a.systemnode_oper_change, a.level_change,a.system_change,a.node_change_info
         %s %s %s """ % (tb.where(['a.systemnode_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '异常', "warn": str(e)})


@app.route('/api/sync/systemNode/new_list_delete', methods=['POST'])
@auth.permission('systemNode')
def get_newList_del(_currUser):
    u"""查询同步网站变更详情删除."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_system_node' and e.operating = 'delete' and e.person_id = '%s' and e.state = '1'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
            from(SELECT a.sys_node_id as asys_node_id, 
            a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, a.systemnode_type_change as systemnode_type_change, 
            a.systemnode_tag_change as systemnode_tag_change,a.systemnode_oper_change as systemnode_oper_change, a.level_change as level_change,
            a.node_change_info as anode_change_info, 
            d2t(e.r_time) as er_time, 
            a.system_change as asystem_change
            from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s group by e.r_time, a.sys_node_id,a.systemnode_name_change ,a.systemnode_code_change,a.systemnode_type_change,a.systemnode_tag_change,a.systemnode_oper_change, a.level_change,a.system_change,a.node_change_info) a """ % (tb.where(['a.systemnode_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT a.sys_node_id as asys_node_id, a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, a.systemnode_type_change as systemnode_type_change, 
        a.systemnode_tag_change as systemnode_tag_change,a.systemnode_oper_change as systemnode_oper_change,
        a.level_change ||'级' as level_change,
        d2t(e.r_time) as er_time,
        a.system_change as asystem_change,
        a.node_change_info as anode_change_info
        from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s 
         group by e.r_time, a.sys_node_id,a.systemnode_name_change ,a.systemnode_code_change,a.systemnode_type_change,a.systemnode_tag_change,a.systemnode_oper_change, a.level_change,a.system_change,a.node_change_info
         %s %s %s
        """ % (tb.where(['a.systemnode_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception,e:
        return json.dumps({"status": "except", 'msg': str(e)})


@app.route('/api/sync/systemNode/list_add', methods=['POST'])
@auth.permission('systemNode')
def get_List_add(_currUser):
    u"""查询历史网站变更详情新增."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_system_node' and e.operating = 'add' and e.person_id = '%s' and e.state = '0'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
            from(SELECT a.sys_node_id as asys_node_id,
             a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, a.systemnode_type_change as systemnode_type_change, 
             a.systemnode_tag_change as systemnode_tag_change,a.systemnode_oper_change as systemnode_oper_change, a.level_change as level_change, 
            a.node_change_info as node_change_info, 
            d2t(e.r_time) as er_time, 
            a.system_change as asystem_change
            from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s) a """ % (tb.where(['a.systemnode_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT a.sys_node_id as asys_node_id, a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, a.systemnode_type_change as systemnode_type_change, 
        a.systemnode_tag_change as systemnode_tag_change,a.systemnode_oper_change as systemnode_oper_change,
        a.level_change ||'级' as level_change,
        a.node_change_info as node_change_info,
        d2t(e.r_time) as er_time,
        a.system_change as asystem_change,
        a.node_change_info as anode_change_info
        from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s %s %s %s """ % (tb.where(['a.systemnode_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", 'msg': '异常', "warn": str(e)})


@app.route('/api/sync/systemNode/list_update', methods=['POST'])
@auth.permission('systemNode')
def get_List_edit(_currUser):
    u"""查询历史网站变更详情更改."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_system_node' and e.operating = 'update' and e.person_id = '%s' and e.state = '0'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
            from(SELECT a.sys_node_id as asys_node_id, 
            a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, a.systemnode_type_change as systemnode_type_change, 
            a.systemnode_tag_change as systemnode_tag_change,a.systemnode_oper_change as systemnode_oper_change, a.level_change as level_change,
            a.node_change_info as node_change_info, 
            d2t(e.r_time) as er_time, 
            a.system_change as asystem_change
            from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s group by e.r_time, a.sys_node_id,a.systemnode_name_change ,a.systemnode_code_change,a.systemnode_type_change,a.systemnode_tag_change,a.systemnode_oper_change, a.level_change,a.system_change,a.node_change_info ) a """ % (tb.where(['a.systemnode_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT 
        a.sys_node_id as asys_node_id,
        a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, 
        a.systemnode_tag_change as systemnode_tag_change,a.systemnode_type_change as systemnode_type_change, a.systemnode_oper_change as systemnode_oper_change,
        a.level_change ||'级' as level_change,
        a.node_change_info as node_change_info,
        d2t(e.r_time) as er_time,
        a.system_change as asystem_change,
        a.node_change_info as anode_change_info
        from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s
        group by e.r_time, a.sys_node_id,a.systemnode_name_change ,a.systemnode_code_change,a.systemnode_type_change,a.systemnode_tag_change,a.systemnode_oper_change, a.level_change,a.system_change,a.node_change_info 
        %s %s %s """ % (tb.where(['a.systemnode_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


@app.route('/api/sync/systemNode/list_delete', methods=['POST'])
@auth.permission('systemNode')
def get_List_del(_currUser):
    u"""查询历史网站变更详情删除."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_system_node' and e.operating = 'delete' and e.person_id = '%s' and e.state = '0'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
            from(SELECT a.sys_node_id as asys_node_id,
             a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, a.systemnode_type_change as systemnode_type_change, 
             a.systemnode_tag_change as systemnode_tag_change,a.systemnode_oper_change as systemnode_oper_change, a.level_change as level_change, 
            a.node_change_info as node_change_info, 
            d2t(e.r_time) as er_time, 
            a.system_change as asystem_change 
            from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s group by e.r_time, a.sys_node_id,a.systemnode_name_change ,a.systemnode_code_change,a.systemnode_type_change,a.systemnode_tag_change,a.systemnode_oper_change, a.level_change,a.system_change,a.node_change_info) a """ % (tb.where(['a.systemnode_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT a.sys_node_id as asys_node_id, a.systemnode_name_change as systemnode_name_change, a.systemnode_code_change as systemnode_code_change, 
        a.systemnode_tag_change as systemnode_tag_change,a.systemnode_type_change as systemnode_type_change, a.systemnode_oper_change as systemnode_oper_change,
        a.level_change ||'级' as level_change,
        a.node_change_info as node_change_info,
        d2t(e.r_time) as er_time,
        a.system_change as asystem_change,
        a.node_change_info as anode_change_info
        from h_systemnode_synchroniz_info as a
        left join h_synchroniz_info as e on e.r_time = a.r_time %s
        group by e.r_time, a.sys_node_id,a.systemnode_name_change ,a.systemnode_code_change,a.systemnode_type_change,a.systemnode_tag_change,a.systemnode_oper_change, a.level_change,a.system_change,a.node_change_info
        %s %s %s """ % (tb.where(['a.systemnode_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


@app.route('/api/sync/systemNode/delete_list', methods=['POST'])
@auth.permission('systemNode')
def delete_important_list(_currUser):
    u"""同步信息标记已读"""
    try:
        g.cursor.execute("""update  h_synchroniz_info set state = '0' where synchroniz_table ='h_system_node' and person_id = '%s' """ % (_currUser['user']['person_id']))
        g.conn.commit()
        return json.dumps({
            "status": "success"
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


# 同步新增数据
@app.route('/api/sync/systemNode/add', methods=['POST'])
def add_website():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            sys_node_id=['系统节点编号', V.required(), V.maxlength(50)],
            sys_node_name=['系统节点名称', V.required(), V.maxlength(100)],
            sys_node_code=['系统节点代码', V.required(), V.maxlength(50)],
            sys_node_tag=['系统节点标签', V.optional(), V.maxlength(20)],
            node_type=['节点类型', V.required(), V.maxlength(50)],
            sys_nodetype_id=['类型编号', V.required(), V.maxlength(50)],
            sys_type_desc=['类型描述', V.optional(), V.maxlength(512)],
            oprate_system=['操作系统', V.optional(''), V.maxlength(50)],
            dns=['DNS', V.optional(), V.maxlength(100)],
            confi_lv_id=['等保级别', V.optional()],
            sys_node_des=['系统节点描述', V.optional(), V.maxlength(512)],
            maintainer=['维护人', V.optional(), V.maxlength(20)],
            maintainer_phone=['维护人电话', V.optional(), V.pattern('^1[0-9]{10}$')],
            maintainer_email=['维护人邮箱', V.optional(), V.email()],
            info_sys_id=['所属系统id', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E1'
            data['msg'] = {'errorKey': data.pop('_key'), 'message': data['msg']}
            return json.dumps(data)
        g.cursor.execute("""select count(sys_node_id) as nodenum from h_system_node where sys_node_id=%(sys_node_id)s and state='1' and is_register='1'""", (data))
        nodenum = g.cursor.fetchone()['nodenum']
        if nodenum > 0:
            return json.dumps({"status": "E2", "msg": {'errorKey': 'sys_node_id', 'message': '此条系统节点已存在'}})
        for item in data['info_sys_id']:
            g.cursor.execute("""select info_sys_id from h_information_system where info_sys_id = '%s' and state='1'""" % item)
            info_sys_id = g.cursor.fetchone()
            if info_sys_id is None:
                return json.dumps({"status": "E2", "msg": {'errorKey': 'info_sys_id', 'message': '信息系统不存在'}})
        level = ["1", "2", "3", "4", "5"]
        if not data['confi_lv_id']:
            data['confi_lv_id'] = '3'
        else:
            if data['confi_lv_id'] not in level:
                return json.dumps({"status": "E2", "msg": {'errorKey': 'confi_lv_id', 'message': '等保级别请输入1-5'}})
        # 插入节点类型表   
        g.cursor.execute("""select count(sys_nodetype_id) as countnum from h_sysnode_type where sys_nodetype_id = %(sys_nodetype_id)s""", data)
        countnum = g.cursor.fetchone()['countnum']
        if countnum == 0: 
            g.cursor.execute("""
                INSERT INTO h_sysnode_type(
                sys_nodetype_id, sys_type_name, sys_type_desc, state)
                VALUES (%(sys_nodetype_id)s, %(node_type)s, %(sys_type_desc)s, '1');
                """, data)
        # 未配置节点
        g.cursor.execute("""select count(sys_node_id) as num
                            from h_system_node
                            where sys_node_id=%(sys_node_id)s and state='1' and is_register='0'""", (data))
        num = g.cursor.fetchone()['num']
        if num > 0:
            for items in data["info_sys_id"]:
                g.cursor.execute("""
                        update h_system_node set
                                is_register = '1'
                                sys_node_name = '%s',
                                sys_node_code ='%s',
                                sys_node_tag = '%s',
                                dns = '%s',
                                confi_lv_id = '%s',
                                sys_node_des = '%s',
                                node_type = '%s',
                                oprate_system = '%s',
                                maintainer = '%s',
                                maintainer_phone = '%s',
                                maintainer_email = '%s',
                                info_sys_id = '%s'
                                where sys_node_id = '%s' and state='1'
                """ % data['sys_node_name'], data['sys_node_code'], data['sys_node_tag'], data['dns'], data['confi_lv_id'], data['sys_node_des'], ['node_type'], data['oprate_system'], data['maintainer'], data['maintainer_phone'], data['maintainer_email'], items, data['sys_node_id'])
                g.cursor.execute("""insert into h_systemnode_synchroniz_info (sys_node_id,
                    systemnode_name_change,
                    systemnode_code_change,
                    systemnode_tag_change,
                    dns_change,
                    level_change,
                    systemnode_desc_change,
                    systemnode_type_change,
                    systemnode_oper_change,
                    in_charge_change,
                    in_charge_phone_change,
                    in_charge_email_change,
                    node_change_info,
                    system_change)
                    values
                    ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                    (select b.agency_name from h_information_system a
                    left join sys_agency_info b on a.agency_id = b.agency_id where a.info_sys_id = '%s'),
                    (select info_sys_name from h_information_system where info_sys_id = '%s'))""" % (
                    data['sys_node_id'], data['sys_node_name'], data['sys_node_code'], data['sys_node_tag'], data['dns'], data['confi_lv_id'],
                    data['sys_node_des'], data['node_type'], data['oprate_system'], data['maintainer'], data['maintainer_phone'], data['maintainer_email'], item, item)
                )
                g.conn.commit()
        else:
            for item in data["info_sys_id"]:
                g.cursor.execute("""insert into h_system_node
                                    (is_register,sys_node_id, info_sys_id, state,sys_node_name, sys_node_code, sys_node_tag, dns, confi_lv_id, sys_node_des, node_type, oprate_system, maintainer, maintainer_phone, maintainer_email, data_source)
                                        values
                                    ('1', '%s','%s', '1', '%s','%s','%s','%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', '0')
                """ % (data['sys_node_id'], item, data['sys_node_name'], data['sys_node_code'], data['sys_node_tag'], data['dns'], data['confi_lv_id'], data['sys_node_des'], data['node_type'], data['oprate_system'], data['maintainer'], data['maintainer_phone'], data['maintainer_email']))          
                g.cursor.execute("""insert into h_systemnode_synchroniz_info (sys_node_id,
                systemnode_name_change,
                systemnode_code_change,
                systemnode_tag_change,
                dns_change,
                level_change,
                systemnode_desc_change,
                systemnode_type_change,
                systemnode_oper_change,
                in_charge_change,
                in_charge_phone_change,
                in_charge_email_change,
                node_change_info,
                system_change)
                values
                ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                (select b.agency_name from h_information_system a
                left join sys_agency_info b on a.agency_id = b.agency_id where a.info_sys_id = '%s'),
                (select info_sys_name from h_information_system where info_sys_id = '%s'))""" % (
                data['sys_node_id'], data['sys_node_name'], data['sys_node_code'], data['sys_node_tag'], data['dns'], data['confi_lv_id'],
                data['sys_node_des'], data['node_type'], data['oprate_system'], data['maintainer'], data['maintainer_phone'], data['maintainer_email'], item, item)
                )
                g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception, e:
        return json.dumps({"status": "E0", 'msg': '异常', "warn": str(e)})


# 同步更改网站信息
@app.route('/api/sync/systemNode/update', methods=['POST'])
def update_website():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            sys_node_id=['系统节点编号', V.required(), V.maxlength(50)],
            sys_node_name=['系统节点名称', V.required(), V.maxlength(100)],
            sys_node_code=['系统节点代码', V.required(), V.maxlength(50)],
            sys_node_tag=['系统节点标签', V.optional(), V.maxlength(20)],
            sys_nodetype_id=['类型编号', V.required(), V.maxlength(50)],
            sys_type_desc=['类型描述', V.optional(), V.maxlength(512)],
            node_type=['节点类型', V.required(), V.maxlength(50)],
            oprate_system=['操作系统', V.optional(''), V.maxlength(50)],
            dns=['DNS', V.optional(), V.maxlength(100)],
            confi_lv_id=['等保级别', V.optional()],
            sys_node_des=['系统节点描述', V.optional(), V.maxlength(512)],
            maintainer=['维护人', V.optional(), V.maxlength(20)],
            maintainer_phone=['维护人电话', V.optional(), V.pattern('^1[0-9]{10}$')],
            maintainer_email=['维护人邮箱', V.optional(), V.email()],
            info_sys_id=['所属系统id', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E1'
            data['msg'] = {'errorKey': data.pop('_key'), 'message': data['msg']}
            return json.dumps(data)
        # g.cursor.execute("""select sys_node_id from h_system_node where sys_node_id=%(sys_node_id)s and state='1'""", (data))
        # sys_node_id = g.cursor.fetchone()
        # if sys_node_id is None:
        #     return json.dumps({"status": "E2", "msg": {'errorKey': 'sys_node_id', 'message': '需要更改的系统节点不存在'}})
        g.cursor.execute("""select sys_node_id from h_system_node where sys_node_id=%(sys_node_id)s and state='1' and sys_node_id != %(sys_node_id)s""", (data))
        website_name = g.cursor.fetchone()
        if website_name is not None:
            return json.dumps({
                "status": "E2",
                "msg": {'errorKey': 'sys_node_id', 'message': '此条系统节点已存在'}
            })
        for item in data['info_sys_id']:
            g.cursor.execute("""select info_sys_id from h_information_system where info_sys_id = '%s' and state='1'""" % item)
            info_sys_id = g.cursor.fetchone()
            if info_sys_id is None:
                return json.dumps({"status": "E2", "msg": {'errorKey': 'info_sys_id', 'message': '信息系统不存在'}})
        level = ["1", "2", "3", "4", "5"]
        if not data['confi_lv_id']:
            data['confi_lv_id'] = '3'
        else:
            if data['confi_lv_id'] not in level:
                return json.dumps({"status": "E2", "msg": {'errorKey': 'confi_lv_id', 'message': '等保级别请输入1-5'}})
        g.cursor.execute("""update h_system_node set 
        sys_node_name = %(sys_node_name)s,
        sys_node_code =%(sys_node_code)s,
        sys_node_tag = %(sys_node_tag)s,
        dns = %(dns)s, 
        confi_lv_id = %(confi_lv_id)s,
        sys_node_des = %(sys_node_des)s, 
        node_type = %(node_type)s,
        oprate_system = %(oprate_system)s,
        maintainer = %(maintainer)s,
        maintainer_phone = %(maintainer_phone)s,
        maintainer_email = %(maintainer_email)s,
        update_time = now() where sys_node_id = %(sys_node_id)s and state='1' """, (data))
        for item in data["info_sys_id"]:
            g.cursor.execute("""insert into h_systemnode_synchroniz_info (sys_node_id,
                systemnode_name_change,
                systemnode_code_change,
                systemnode_tag_change,
                dns_change,
                level_change,
                systemnode_desc_change,
                systemnode_type_change,
                systemnode_oper_change,
                in_charge_change,
                in_charge_phone_change,
                in_charge_email_change,
                node_change_info,
                system_change)
                values
                ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                (select b.agency_name from h_information_system a
                left join sys_agency_info b on a.agency_id = b.agency_id where a.info_sys_id = '%s'),
                (select info_sys_name from h_information_system where info_sys_id = '%s'))""" % (
                data['sys_node_id'], data['sys_node_name'], data['sys_node_code'], data['sys_node_tag'], data['dns'], data['confi_lv_id'],
                data['sys_node_des'], data['node_type'], data['oprate_system'], data['maintainer'], data['maintainer_phone'], data['maintainer_email'], item, item)
                )
        g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception, e:
        return json.dumps({"status": "E0", 'msg': '异常', "warn": str(e)})


# 同步删除数据
@app.route('/api/sync/systemNode/delete', methods=["POST"])
def delete():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            sys_node_id=["系统节点编号", V.required(), V.maxlength(50)]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E1'
            data['msg'] = {'errorKey': data.pop('_key'), 'message': data['msg']}
            return json.dumps(data)
        g.cursor.execute("""update h_system_node set state = '0', update_time = now() where  sys_node_id = %(sys_node_id)s and state = '1'""", (data))
        g.cursor.execute("""
            select c.sys_node_id as sys_node_id,
                    c.sys_node_name as systemnode_name_change,
                    c.sys_node_code as systemnode_code_change,
                    c.sys_node_tag as systemnode_tag_change,
                    c.dns as dns_change,
                    c.confi_lv_id as level_change,
                    c.sys_node_des as systemnode_desc_change,
                    c.node_type as systemnode_type_change,
                    c.oprate_system as systemnode_oper_change,
                    c.maintainer as in_charge_change,
                    c.maintainer_phone as in_charge_phone_change,
                    c.maintainer_email as in_charge_email_change,
                    b.info_sys_name  as system_change,
                    a.agency_name as node_change_info
            from sys_agency_info a 
            LEFT JOIN h_information_system b on a.agency_id = b.agency_id
            left join h_system_node c on b.info_sys_id = c.info_sys_id
            where c.sys_node_id = %(sys_node_id)s
        """,data)
        dataSource = g.cursor.fetchall()
        for item in dataSource:
            g.cursor.execute("""INSERT into
                                h_systemnode_synchroniz_info (
                                sys_node_id,
                                systemnode_name_change,
                                systemnode_code_change,
                                systemnode_tag_change,
                                dns_change,
                                level_change,
                                systemnode_desc_change,
                                systemnode_type_change,
                                systemnode_oper_change,
                                in_charge_change,
                                in_charge_phone_change,
                                in_charge_email_change,
                                system_change,
                                node_change_info
                                ) values 
                                (%(sys_node_id)s,%(systemnode_name_change)s,%(systemnode_code_change)s,%(systemnode_tag_change)s,%(dns_change)s,%(level_change)s,
                                %(systemnode_desc_change)s,%(systemnode_type_change)s,%(systemnode_oper_change)s,%(in_charge_change)s,
                                %(in_charge_phone_change)s,%(in_charge_email_change)s,%(system_change)s,%(node_change_info)s)""", (item))
        g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception, e:
        return json.dumps({"status": "E0", 'msg': '异常', "warn": str(e)})
